#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time:               2018/8/5 11:14
# @Author:            Joey66666
# @Software         VSCode

import json
import logging
import time
import base64

import datetime

from decimal import *

from flask import Flask, jsonify, abort, request
from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import generate_password_hash, check_password_hash

from sqlalchemy import create_engine, MetaData, create_engine, MetaData, Table, Column, Date, Integer, String, \
    ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import \
    BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, DATE, \
    DATETIME, DECIMAL, DECIMAL, DOUBLE, ENUM, FLOAT, INTEGER, \
    LONGBLOB, LONGTEXT, MEDIUMBLOB, MEDIUMINT, MEDIUMTEXT, NCHAR, \
    NUMERIC, NVARCHAR, REAL, SET, SMALLINT, TEXT, TIME, TIMESTAMP, \
    TINYBLOB, TINYINT, TINYTEXT, VARBINARY, VARCHAR, YEAR

app = Flask('test')

# # 配置数据库连接
# app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+mysqlconnector://root:gushiteng123@localhost:3306/leavy_the_school_system'
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+mysqlconnector://leavy_system:zhiy_buzao_acxz@gushiteng.run:3306/leavy_system'
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db = SQLAlchemy(app)


@app.route('/')
def hello():
    return "LeaveMiniprogram-Api page"


# @app.route("/login", methods=['GET', 'POST'])
# def login():
#     if (request.method == 'POST'):
#         if not (request.json):
#             return jsonify('not json')
#         else:
#             data = request.get_json()
#
#             rec_id = data['userId']
#             rec_pwd = data['userPwd']
#
#             if len(str(rec_id)) == 6:
#                 db_data = search_t(str(rec_id))
#                 if (db_data) is None:
#                     return_data = dict()
#                     return_data['flag'] = '0'
#                     return jsonify(return_data)
#                 else:
#                     db_id = str(
#                         Decimal(db_data['teacher_id']).quantize(Decimal('0')))
#                     db_name = db_data['name']
#                     # db_pwd = db_data['passwd']
#                     db_role = db_data['role']
#                     db_school = db_data['school']
#
#                     if check_password_hash(str(db_pwd), str(rec_pwd)) is not True:
#                         return_data = dict()
#                         return_data['flag'] = '1'
#                         return_data['teacher_id'] = rec_id
#                         return jsonify(return_data)
#                     elif check_password_hash(str(db_pwd), str(rec_pwd)) is True:
#                         return_data = dict()
#                         db_id = str(
#                             Decimal(db_data['teacher_id']).quantize(Decimal('0')))
#                         return_data['teacher_id'] = db_id
#                         return_data['name'] = db_name
#                         return_data['role'] = db_role
#                         return_data['school'] = db_school
#                         return_data['flag'] = '2'
#
#                         return (jsonify(return_data))
#             if len(str(rec_id)) == 9:
#                 db_data = search_s(str(rec_id))
#                 if (db_data) is None:
#                     return_data = dict()
#                     return_data['flag'] = '0'
#                     return jsonify(return_data)
#
#                 else:
#                     db_id = str(
#                         Decimal(db_data['student_id']).quantize(Decimal('0')))
#                     db_name = db_data['name']
#                     db_pwd = db_data['passwd']
#                     db_class = db_data['s_class']
#                     db_room = db_data['room']
#                     if check_password_hash(str(db_pwd), str(rec_pwd)) is not True:
#                         return_data = dict()
#                         return_data['flag'] = '1'
#                         return_data['student_id'] = rec_id
#                         return jsonify(return_data)
#
#
#                     elif check_password_hash(str(db_pwd), str(rec_pwd)) is True:
#                         return_data = dict()
#                         db_id = str(
#                             Decimal(db_data['student_id']).quantize(Decimal('0')))
#                         return_data['student_id'] = db_id
#                         return_data['name'] = db_name
#                         return_data['s_class'] = db_data['s_class']
#                         return_data['room'] = db_data['room']
#                         return_data['flag'] = '2'
#                         return (jsonify(return_data))
#
#             else:
#
#                 return_data = dict()
#                 return_data['flag'] = '0'
#                 return jsonify(return_data)
#
#
#     else:
#         return jsonify('not POST method')


@app.route("/read_leavy_deatil_by_leavyid", methods=['GET', 'POST'])
def read_leavy_deatil_by_leavyid():
    if (request.method == 'GET'):
        # if not (request.json):
        #     return jsonify('not json')
        # else:
        rec_leavyid = request.args.get('leavyid')
        print('138-138 route:::::/read_leavy_deatil_by_leavyid:::rec_leavyid: ', rec_leavyid)
        # record = db.session.query(Leave).filter_by(leavyid=rec_leavyid).first()
        record_list = db.session.execute(
                """
                SELECT
                    l.leavyid as leavyid,
                    l.start_time as stime,
                    l.end_time as etime,
                    l.flag as flag,
                    s.s_grade as grade,
                    s.s_class as cla,
                    s.name as name,
                    s.room as room,
                    s.student_id as student_id,
                    l.reason as reason,
                    l.ensure as ensure
                FROM student s JOIN leavy l ON l.student_id = s.student_id
                WHERE l.leavyid = '{rec_leavyid}' 
                limit 1;
                """.format(rec_leavyid=rec_leavyid)
            )
        if not record_list:
            # return jsonify('record type error... no record')
            print('no record')
            return jsonify('no record')
        record_list = list(record_list)
        print('''202-202 read_user_by_wxopenid:::method record_list: ''', record_list)
        record = None
        print('''297-297 read_user_by_wxopenid:::method in record_list:: record ''', record_list)
        for item in record_list:
            record = item
            print('''297-297 read_user_by_wxopenid:::method in record_list:: record ''', record)
            break
        if not record:
            print('wild leavyid')
            return jsonify('wild leavyid')

        # print('140-140 route:::::/read_leavy_deatil_by_leavyid:::record: ', record)
        # keys = Leave.__table__.columns.keys()
        # record = {record.getattr(record, key) for key in keys}
        # data = record.to_dict()

        leavyid, stime_array, etime_array, flag, grade, cla, name, room, student_id, reason, ensure = record
        data = {}
        data['leavyid'] = leavyid
        data['stime'] = stime_array.strftime("%Y-%m-%d %H:%M:%S")
        data['etime'] = etime_array.strftime("%Y-%m-%d %H:%M:%S")
        data['flag'] = flag
        data['grade'] = grade
        data['cla'] = cla
        data['name'] = name
        data['room'] = room
        data['student_id'] = student_id
        data['reason'] = reason
        data['ensure'] = ensure
        return jsonify(data)
    else:
        return jsonify('not GET method')


@app.route("/student/ask_leavy", methods=['GET', 'POST'])
def ask_leavy():
    if (request.method == 'POST'):
        if not (request.json):
            return jsonify('not json')
        else:
            try:
                data = request.get_json()

                student_id = data['student_id']
                start_time = data['start_time']
                end_time = data['end_time']
                reason = data['reason']
                flag = data['flag']
                # teacher1_id = data['teacher1_id']
                teacher2_id = data['teacher2_id']
                s_type = data['type']
                ensure = data['ensure']
                print('149-149 function:::::ask_leavy:::data:', data)

                # insert = insert_leavy(student_id, start_time, end_time, reason, flag, teacher1_id, teacher2_id, s_type,
                insert = insert_leavy(student_id, start_time, end_time, reason, flag, teacher2_id, s_type,
                                      ensure)
                if (insert == True):
                    return ('True')
                else:
                    return ('False')

            except Exception:
                return ("ERROR")

    else:
        return jsonify('not POST method')


@app.route("/student/search_leavy", methods=['GET', 'POST'])
def search_s_leavy():
    if request.method == 'POST':
        if not (request.json):
            return jsonify('not json')
        else:
            data = request.get_json()
            student_id = data['student_id']
            result = search_stu_leavy(student_id)
            return (result)

    else:
        return jsonify("not POST")


@app.route("/student/search_leavy_detail", methods=['GET', 'POST'])
def search_s_leavy_detail():
    if request.method == 'POST':
        if not (request.json):
            return jsonify('not json')
        else:
            data = request.get_json()
            student_id = data['student_id']
            result = search_stu_leavy_detail(student_id)
            return (result)

    else:
        return jsonify("not POST")


@app.route("/teacher/search_leavy", methods=['GET', 'POST'])
def search_t_leavy():
    if request.method == 'POST':
        if not (request.json):
            return jsonify('not json')
        else:
            data = request.get_json()
            teacher_id = data['teacher_id']
            result = search_tea_leavy(teacher_id)
            return (result)

    else:
        return jsonify("not POST")


@app.route("/teacher2/search_leavy", methods=['GET', 'POST'])
def search_t2_leavy():
    if request.method == 'POST':
        if not (request.json):
            return jsonify('not json')
        else:
            data = request.get_json()
            teacher_id = data['teacher_id']
            result = search_tea2_leavy(teacher_id)
            return (result)

    else:
        return jsonify("not POST")


@app.route("/teacher/update_leavy", methods=['GET', 'POST'])
def update_leavy():
    if request.method == 'POST':
        if not (request.json):
            return jsonify('not json')
        else:
            data = request.get_json()
            leavyid = data['leavyid']
            flag = data['flag']
            result = update_leavy(leavyid, flag)
            return (result)

    else:
        return jsonify("not POST")


@app.route("/teacher/search_id", methods=['GET', 'POST'])
def search_t_id():
    if request.method == 'POST':
        if not (request.json):
            return jsonify('not json')
        else:
            data = request.get_json()
            school = data['school']
            result = search_t_id(school)
            return (result)

    else:
        return jsonify("not POST")


@app.route("/read_user_by_wxopenid", methods=["GET", "POST"])
def read_user_by_wxopenid():
    if (request.method == 'GET'):
        # if not request.json:
        #     return jsonify('not json')
        # else:
        try:
            rec_wxopenid = request.args.get('wxopenid')
            record_list = db.session.execute(
                """
                SELECT
                    s.student_id as s_student_id, 
                    s.wxopenid as s_wxopenid, 
                    s.name as s_name,
                    s.role as s_role,
                    s.s_grade as s_s_grade, 
                    s.s_class as s_s_class, 
                    s.room as s_room, 
                    s.school as s_school, 
                    t.teacher_id as t_teacher_id,
                    t.wxopenid as t_wxopenid, 
                    t.name as t_name, 
                    t.role as t_role, 
                    t.school as t_school  
                FROM student s LEFT JOIN teacher t ON s.wxopenid <> t.wxopenid
                UNION
                SELECT
                    s.student_id as s_student_id, 
                    s.wxopenid as s_wxopenid, 
                    s.name as s_name,
                    s.role as s_role,
                    s.s_grade as s_s_grade, 
                    s.s_class as s_s_class, 
                    s.room as s_room, 
                    s.school as s_school, 
                    t.teacher_id as t_teacher_id,
                    t.wxopenid as t_wxopenid, 
                    t.name as t_name, 
                    t.role as t_role, 
                    t.school as t_school                  
                FROM student s right JOIN teacher t ON s.wxopenid <> t.wxopenid
                WHERE s.wxopenid='{wxopenid}' or t.wxopenid='{wxopenid}'
                limit 1;
                """.format(wxopenid=rec_wxopenid)
            )
            if not record_list:
                # return jsonify('record type error... no record')
                print('no record')
                return jsonify('no record')
            # record_list = list(record_list)
            # print('''202-202 read_user_by_wxopenid:::method record_list: ''', record_list)
            record = None
            for item in record_list:
                print('''297-297 read_user_by_wxopenid:::method in record_list:: record ''', record)
                record = item
                break
            if not record:
                print('wild wxopenid')
                return jsonify('wild wxopenid')
            s_student_id, s_wxopenid, s_name, s_role, s_s_grade, s_s_class, s_room, s_school, t_teacher_id, t_wxopenid, t_name, t_role, t_school = record
            # break

            print('292-292 read_user_by_wxopenid:::record: ', record)
            data = {}
            data['wxopenid'] = rec_wxopenid
            if record['s_wxopenid'] == rec_wxopenid:
                data['school'] = s_school
                data['name'] = s_name
                data['role'] = s_role
                data['userid'] = s_student_id
                data['student_id'] = s_student_id
                data['s_grade'] = s_s_grade
                data['s_class'] = s_s_class
                data['room'] = s_room
                return jsonify(data)
            elif record['t_wxopenid'] == rec_wxopenid:
                data['school'] = t_school
                data['name'] = t_name
                data['role'] = t_role
                data['teacher_id'] = t_teacher_id
                data['userid'] = t_teacher_id
                data['role'] = t_role
                return jsonify(data)
            else:
                return jsonify('wild wxopenid')
        except Exception as e:
            print('273-273 function:::::read_user::::type(e):::e:: {}:{}'.format(type(e), e))
            return jsonify("read_user_by_wxopenid method False")


@app.route("/edit_user", methods=['GET', 'POST'])
def edit_user():
    if (request.method == 'POST'):
        if not (request.json):
            return jsonify('not json')
        else:
            try:
                data = request.get_json()
                print('326-326 data:', data)
                rec_id = data['userid']
                rec_wxopenid = data['wxopenid']
                rec_name = data['name']
                rec_school = data['school']
                rec_room = ''
                rec_s_grade = 1
                rec_s_class = 1
                rec_role = data['role']
                if rec_role == '11':
                    rec_s_grade = data['s_grade']
                    rec_s_class = data['s_class']
                    rec_room = data['room']
                save_ret = save_user(userId=rec_id, wxopenid=rec_wxopenid, name=rec_name,
                                     s_grade=rec_s_grade,
                                     s_class=rec_s_class,
                                     school=rec_school, role=rec_role, room=rec_room)
                print('368-368 save_ret:', save_ret)
                return jsonify("edit_user method True")

            except Exception as e:
                print('372-372 type(e):::e:: {}:{}'.format(type(e), e))
                return jsonify("edit_user method False")

    else:
        return jsonify('not POST method')


@app.route("/change_pwd", methods=['GET', 'POST'])
def change_pwd():
    if (request.method == 'POST'):
        if not (request.json):
            return jsonify('not json')
        else:
            try:
                data = request.get_json()
                rec_id = data['userId']
                rec_pwd = data['userPwd']
                change_passwd(rec_id, rec_pwd)
                return jsonify("True")

            except:
                return jsonify("False")

    else:
        return jsonify('not POST method')


@app.route("/search_name", methods=['GET', 'POST'])
def search_name():
    if (request.method == 'POST'):
        if not (request.json):
            return jsonify('not json')
        else:
            # try:
            data = request.get_json()
            rec_name = data['name']
            try:
                return_data = search_name(rec_name)
            except:
                return jsonify("name is not exist")
            else:
                return jsonify(return_data)

    else:
        return jsonify('not POST method')


class Student(db.Model):
    __tablename__ = 'student'
    student_id = db.Column(db.INTEGER, primary_key=True)
    wxopenid = db.Column(db.VARCHAR(255))
    name = db.Column(db.VARCHAR(255))
    role = db.Column(db.VARCHAR(2))
    s_grade = db.Column(db.VARCHAR(255))
    s_class = db.Column(db.VARCHAR(255))
    # passwd = db.Column(db.VARCHAR(255))
    room = db.Column(db.VARCHAR(255))
    school = db.Column(db.VARCHAR(255))

    def to_dict(self):
        return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}


class Teacher(db.Model):
    __tablename__ = 'teacher'
    teacher_id = db.Column(db.INTEGER, primary_key=True)
    wxopenid = db.Column(db.VARCHAR(255))
    name = db.Column(db.VARCHAR(255))
    # passwd = db.Column(db.VARCHAR(255))
    role = db.Column(db.VARCHAR(2))
    school = db.Column(db.VARCHAR(255))

    def to_dict(self):
        return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}


class Leave(db.Model):
    __tablename__ = 'leavy'
    leavyid = db.Column(db.INTEGER, primary_key=True)
    student_id = db.Column(db.INTEGER)
    start_time = db.Column(db.DATETIME)
    end_time = db.Column(db.DATETIME)
    reason = db.Column(db.VARCHAR(255))
    flag = db.Column(db.VARCHAR(2))
    # teacher1_id = db.Column(db.INTEGER)
    teacher2_id = db.Column(db.INTEGER)
    type = db.Column(db.VARCHAR(2))
    ensure = db.Column(db.VARCHAR(255))

    def to_dict(self):
        return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}

    # 使用转化记录的 dict 类型的 详细讲解： https://www.cnblogs.com/sanduzxcvbnm/p/10220718.html
    # def single_to_dict(self):
    #     return {c.name: getattr(self, c.name) for c in self.__table__.columns}
    #
    # # 多个对象
    # def dobule_to_dict(self):
    #     result = {}
    #     for key in self.__mapper__.c.keys():
    #         if getattr(self, key) is not None:
    #             result[key] = str(getattr(self, key))
    #         else:
    #             result[key] = getattr(self, key)
    #     return result


class Location(db.Model):
    __tablename__ = 'location'

    location_id = db.Column(db.INTEGER, primary_key=True)
    wxopenid = db.Column(db.VARCHAR(255))
    addr_str = db.Column(db.VARCHAR(255))
    addr_info_json = db.Column(db.TEXT)
    addr_info_json = db.Column(db.TEXT)

    def to_dict(self):
        return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}


# 字符串转二进制
def b_encode(s):
    return (''.join([bin(ord(c)).replace('0b', '') for c in s]))


# 二进制转字符串
def b_decode(s):
    return (''.join([chr(i) for i in [int(b, 2) for b in s.split('')]]))


def search_t(id):
    result = Teacher.query.filter_by(teacher_id=id).first()
    if result is None:
        return (None)
    else:
        return (result.to_dict())


def search_s(id):
    result = Student.query.filter_by(student_id=id).first()
    if result is None:
        return (None)
    else:
        return (result.to_dict())


def insert_leavy(student_id, start_time, end_time, reason, flag, teacher2_id, s_type, ensure):
    engine = create_engine(
        app.config['SQLALCHEMY_DATABASE_URI']
    )
    # 'mysql+mysqlconnector://user:passwd@IP:port/DB'
    metadata = MetaData(engine)
    # 连接数据表
    leavy_table = Table('leavy', metadata, autoload=True)
    try:  # 创建 insert 对象
        ins = leavy_table.insert()
        # 绑定要插入的数据
        ins = ins.values(student_id=student_id, start_time=start_time, end_time=end_time, reason=reason, flag=flag,
                         teacher2_id=teacher2_id, type=s_type, ensure=ensure)
        # 连接引擎
        conn = engine.connect()
        # 执行语句
        result = conn.execute(ins)
    except Exception as e:
        print('525-525 insert_leavy raise Exception as e:', e)
        return (False)
    else:
        print(result)
        return (True)


def search_stu_leavy(id):
    result = Leave.query.filter_by(student_id=id).all()
    if result is None:
        return (None)
    else:
        result_length = len(result)
        return_data = {}
        i = 0
        while i < result_length:
            temp_data = dict()

            leavyid = result[i].to_dict()['leavyid']
            start_time = str(result[i].to_dict()['start_time'])
            end_time = str(result[i].to_dict()['end_time'])
            reason = result[i].to_dict()['reason']
            flag = result[i].to_dict()['flag']
            # teacher1_id = str(Decimal(result[i].to_dict()[
            # 'teacher1_id']).quantize(Decimal('0')))
            teacher2_id = str(Decimal(result[i].to_dict()[
                                          'teacher2_id']).quantize(Decimal('0')))
            data_type = result[i].to_dict()['type']
            ensure = result[i].to_dict()['ensure']

            temp_data['leavyid'] = leavyid
            temp_data['start_time'] = start_time
            temp_data['end_time'] = end_time
            temp_data['reason'] = reason
            temp_data['flag'] = flag
            # temp_data['teacher1_id'] = teacher1_id
            temp_data['teacher2_id'] = teacher2_id
            temp_data['type'] = data_type
            temp_data['ensure'] = ensure

            return_data[i] = temp_data
            i += 1
        return (jsonify(return_data))


def search_stu_leavy_detail(id):
    result = Student.query.filter_by(student_id=id).all()
    if result is None:
        return (None)
    else:
        result_length = len(result)
        return_data = {}
        i = 0
        while i < result_length:
            temp_data = dict()

            student_id = str(Decimal(result[i].to_dict()[
                                         'student_id']).quantize(Decimal('0')))
            name = result[i].to_dict()['name']
            s_class = result[i].to_dict()['s_class']
            room = result[i].to_dict()['room']

            temp_data['student_id'] = student_id
            temp_data['name'] = name
            temp_data['class'] = s_class
            temp_data['room'] = room
            return_data[i] = temp_data
            i += 1
        return (jsonify(return_data))


def search_tea_leavy(id):
    result = Leave.query.filter_by(teacher2_id=id).all()
    if result is None:
        return (None)
    else:
        result_length = len(result)
        return_data = {}
        i = 0
        while i < result_length:
            temp_data = dict()

            leavyid = result[i].to_dict()['leavyid']
            student_id = str(Decimal(result[i].to_dict()[
                                         'student_id']).quantize(Decimal('0')))
            start_time = str(result[i].to_dict()['start_time'])
            end_time = str(result[i].to_dict()['end_time'])
            reason = result[i].to_dict()['reason']
            flag = result[i].to_dict()['flag']
            # teacher1_id = str(Decimal(result[i].to_dict()['teacher1_id']).quantize(Decimal('0')))
            teacher2_id = str(Decimal(result[i].to_dict()[
                                          'teacher2_id']).quantize(Decimal('0')))
            data_type = result[i].to_dict()['type']
            ensure = result[i].to_dict()['ensure']

            # print(leavyid, start_time, end_time, reason, flag, teacher1_id, teacher2_id, data_type, ensure)
            print(leavyid, start_time, end_time, reason, flag, teacher2_id, data_type, ensure)

            temp_data['leavyid'] = leavyid
            temp_data['student_id'] = student_id
            temp_data['start_time'] = start_time
            temp_data['end_time'] = end_time
            temp_data['reason'] = reason
            temp_data['flag'] = flag
            # temp_data['teacher1_id'] = teacher1_id
            temp_data['teacher2_id'] = teacher2_id
            temp_data['type'] = data_type
            temp_data['ensure'] = ensure

            return_data[i] = temp_data
            i += 1
        return (jsonify(return_data))


def search_tea2_leavy(id):
    result = Leave.query.filter_by(teacher2_id=id).all()
    if result is None:
        return jsonify("None")
    else:
        result_length = len(result)
        return_data = {}
        i = 0
        while i < result_length:
            temp_data = dict()

            leavyid = result[i].to_dict()['leavyid']
            student_id = str(Decimal(result[i].to_dict()[
                                         'student_id']).quantize(Decimal('0')))
            start_time = str(result[i].to_dict()['start_time'])
            end_time = str(result[i].to_dict()['end_time'])
            reason = result[i].to_dict()['reason']
            flag = result[i].to_dict()['flag']
            # teacher1_id = str(Decimal(result[i].to_dict()['teacher1_id']).quantize(Decimal('0')))
            teacher2_id = str(Decimal(result[i].to_dict()[
                                          'teacher2_id']).quantize(Decimal('0')))
            data_type = result[i].to_dict()['type']
            ensure = result[i].to_dict()['ensure']
            temp_data['leavyid'] = leavyid
            temp_data['student_id'] = student_id
            temp_data['start_time'] = start_time
            temp_data['end_time'] = end_time
            temp_data['reason'] = reason
            temp_data['flag'] = flag
            # temp_data['teacher1_id'] = teacher1_id
            temp_data['teacher2_id'] = teacher2_id
            temp_data['type'] = data_type
            temp_data['ensure'] = ensure

            return_data[i] = temp_data
            i += 1
        return (jsonify(return_data))


def search_s_name(name):
    result = Student.query.filter_by(name=name).first()
    if result is None:
        return (None)
    else:
        return (result.to_dict())


def search_name(name):
    student_data = search_s_name(name)
    student_id = str(Decimal(student_data['student_id']).quantize(Decimal('0')))
    student_name = student_data['name']
    student_class = student_data['s_class']
    student_room = student_data['room']
    student_school = student_data['school']

    result = Leave.query.filter_by(student_id=student_id).all()

    if result is None:
        return jsonify("None")
    else:
        result_length = len(result)
        return_data = {}
        i = 0
        while i < result_length:
            temp_data = dict()

            leavyid = result[i].to_dict()['leavyid']
            start_time = str(result[i].to_dict()['start_time'])
            end_time = str(result[i].to_dict()['end_time'])
            reason = result[i].to_dict()['reason']
            flag = result[i].to_dict()['flag']
            teacher1_id = str(Decimal(result[i].to_dict()['teacher1_id']).quantize(Decimal('0')))
            teacher2_id = str(Decimal(result[i].to_dict()['teacher2_id']).quantize(Decimal('0')))
            data_type = result[i].to_dict()['type']
            ensure = result[i].to_dict()['ensure']

            temp_data['leavyid'] = leavyid
            temp_data['start_time'] = start_time
            temp_data['end_time'] = end_time
            temp_data['reason'] = reason
            temp_data['flag'] = flag
            temp_data['teacher1_id'] = teacher1_id
            temp_data['teacher2_id'] = teacher2_id
            temp_data['type'] = data_type
            temp_data['ensure'] = ensure
            temp_data['student_id'] = student_id
            temp_data['class'] = student_class
            temp_data['room'] = student_room
            temp_data['school'] = student_school
            temp_data['name'] = student_name

            return_data[i] = temp_data
            i += 1
        return (return_data)


def update_leavy(leavyid, flag):
    try:
        data = db.session.query(Leave).filter_by(leavyid=leavyid).first()
        data.flag = flag
        db.session.commit()
        db.session.close()
        return jsonify("True")
    except:
        return jsonify("False")


def search_t_id(school):
    result = Teacher.query.filter_by(school=school).all()
    if result is None:
        return jsonify("None")
    else:
        result_length = len(result)
        return_data = {}
        i = 0
        while i < result_length:
            temp_data = dict()

            teacher_id = str(Decimal(result[i].to_dict()[
                                         'teacher_id']).quantize(Decimal('0')))
            name = result[i].to_dict()['name']
            role = result[i].to_dict()['role']
            temp_data['teacher_id'] = teacher_id
            temp_data['name'] = name
            temp_data['role'] = role
            return_data[i] = temp_data
            i += 1
        return (jsonify(return_data))


def save_user(userId, wxopenid, name, s_grade, s_class, school, role, room):
    # role = int(role)
    if role == '11':
        print('''662-662 save_user method (match role == '11' means student) print arguments::
                userId: {userId} ,
                wxopenid: {wxopenid},
                name: {name},
                s_grade: {s_grade},
                s_class: {s_class},
                school: {school},
                role:   {role},
                room:   {room},
                '''.format(userId=userId, wxopenid=wxopenid, name=name, s_grade=s_grade, s_class=s_class, school=school,
                           role=role, room=room))
        try:
            data = db.session.query(Student).filter_by(
                wxopenid=wxopenid).first()
            if not data:
                data = Student(

                    # student_id=userId,
                    wxopenid=wxopenid,
                    name=name,
                    role=role,
                    s_grade=s_grade,
                    s_class=s_class,
                    room=room,
                    school=school

                    # student_id=userId,
                    # wxopenid=wxopenid,
                    # name=name,
                    # s_class=s_class,
                    # # passwd = generate_password_hash(str(passwd)),
                    # school=school,
                    # room=room
                )
                print('789-789 function:::::save_user::::role:::11::ifnotdata:data', data)
                db.session.add(data)
            else:
                # data.student_id = userId
                data.wxopenid = wxopenid
                data.name = name
                data.s_class = s_class
                # data.passwd = generate_password_hash(str(passwd))
                data.school = school
                # data.room = room
                # print('679-679 save_user:::data:', json.dumps(data))
            db.session.commit()
            db.session.close()

            # `student_id` INTEGER(9) NOT NULL PRIMARY key auto_increment UNIQUE KEY,
            # `name` VARCHAR(255) NOT NULL DEFAULT '没个性的学生用户名',
            # `s_class` VARCHAR(255) NOT NULL DEFAULT '1',
            # `passwd` VARCHAR(255) NOT NULL DEFAULT 's123456',
            # `room` VARCHAR(255) NOT NULL DEFAULT '102',
            # `school` VARCHAR(255) NOT NULL DEFAULT 'XX学校'

            # return jsonify("True")
            return 'save_user method return successed...'
        except Exception as e:
            print('821-821 type(e):::e:: {}:{}'.format(type(e), e))
            return 'save_user exeception:::::type(e):::e:: {}:{}'.format(type(e), e)
            # return jsonify("False")
    elif role in ('0', '1'):
        # `teacher_id` INTEGER(6) NOT NULL PRIMARY Key auto_increment UNIQUE KEY,
        # `name` VARCHAR(255) NOT NULL DEFAULT '没个性的教师用户名',
        # `passwd` VARCHAR(255) NOT NULL DEFAULT 't123456',
        # `role` INTEGER(2) NOT NULL DEFAULT 1,
        # `school` VARCHAR(255) NOT NULL DEFAULT 'XX学校'
        print('''662-662 save_user method (match role != '11' means student) teacher print arguments::
                userId: {userId} ,
                wxopenid: {wxopenid},
                name: {name},
                s_grade: {s_grade},
                s_class: {s_class},
                school: {school},
                role:   {role},
                room:   {room},
                '''.format(userId=userId, wxopenid=wxopenid, name=name, s_grade=s_grade, s_class=s_class, school=school,
                           role=role, room=room))
        try:
            data = db.session.query(Teacher).filter_by(
                wxopenid=wxopenid).first()

            if not data:
                data = Teacher(

                    # teacher_id=userId,
                    wxopenid=wxopenid,
                    name=name,
                    role=role,
                    school=school

                    # student_id=userId,
                    # name=name,
                    # role=role,
                    # school=school
                )
                db.session.add(data)
            else:
                data.teacher_id = userId
                data.wxopenid = wxopenid
                data.name = name
                data.role = role
                data.school = school

            db.session.commit()
            db.session.close()
            # data.passwd = generate_password_hash(str(passwd))
            return jsonify("True")
        except:
            return jsonify("False")


def change_passwd(userId, userPwd, role='11'):
    if role in ('0', '1'):
        try:
            data = db.session.query(Teacher).filter_by(
                teacher_id=userId).first()
            data.passwd = generate_password_hash(str(userPwd))
            db.session.commit()
            db.session.close()
            return jsonify("True")
        except:
            return jsonify("False")
    elif role == '11':
        try:
            data = db.session.query(Student).filter_by(
                student_id=userId).first()
            data.passwd = generate_password_hash(str(userPwd))
            db.session.commit()
            db.session.close()
            return jsonify("True")
        except:
            return jsonify("False")


# def list_location():


@app.route("/insert_location_by_wxopenid", methods=["GET", "POST"])
def insert_location_by_wxopenid():
    if (request.method == 'POST'):
        engine = create_engine(
            app.config['SQLALCHEMY_DATABASE_URI']
        )

        data = request.get_json()
        print('884-884 route:::::insert_location_by_wxopenid:::get_json:', data)
        wxopenid = data['wxopenid']
        addr_str = data['addr_str']
        latitude = data['latitude']
        longitude = data['longitude']
        addr_info_json = data['addr_info_json'] or ''
        addr_component_json = data['addr_component_json'] or ''
        addr_pois_json = data['addr_pois_json'] or ''

        metadata = MetaData(engine)
        leavy_table = Table('location', metadata, autoload=True)
        try:  # 创建 insert 对象
            ins = leavy_table.insert()
            # 绑定要插入的数据
            ins = ins.values(
                wxopenid=wxopenid,
                addr_str=addr_str,
                latitude=latitude,
                longitude=longitude,
                addr_info_json=addr_info_json,
                addr_component_json=addr_component_json,
                addr_pois_json=addr_pois_json
            )
            # 连接引擎
            conn = engine.connect()
            # 执行语句
            result = conn.execute(ins)
        except Exception as e:
            print('899-899 insert_location_by_wxopenid type(e):::::e::: {}:{}'.format(type(e), e))
            return (jsonify(False))
        else:
            print(result)
            return jsonify(True)
    else:
        return jsonify('not POST method')


@app.route("/list_location_by_wxopenid", methods=["GET", "POST"])
def list_location_by_wxopenid():
    if (request.method == 'GET'):
        # if not request.json:
        #     return jsonify('not json')
        # else:
        try:
            rec_wxopenid = request.args.get('wxopenid')
            rec_page = request.args.get('page')
            page_size = 20
            page_index = int(rec_page) - 1
            start_record_position = page_index * page_size
            sql_result = db.session.execute(
                """
                SELECT * FROM location
                WHERE wxopenid='{wxopenid}'
                ORDER BY create_time DESC
                LIMIT {start_record_position}, {page_size};
                """.format(wxopenid=rec_wxopenid, start_record_position=start_record_position, page_size=page_size)
            )
            if not sql_result:
                # return jsonify('record type error... no record')
                print('no record')
                return jsonify([])
            # record_list = list(record_list)
            # print('''202-202 read_location_by_wxopenid:::method record_list: ''', record_list)
            record_list = []
            for record in sql_result:
                print('''297-297 list_location_by_wxopenid:::method in record_list:: record ''', record)

                # `location_id` INTEGER(9) NOT NULL PRIMARY key auto_increment UNIQUE KEY,
                # `wxopenid` VARCHAR(32) NOT NULL UNIQUE KEY,
                # `addr_str` VARCHAR(255) NOT NULL DEFAULT '',

                # `latitude` NUMERIC(10,4) NOT NULL,
                # `longitude` NUMERIC(10,4) NOT NULL,
                # `addr_info_json` TEXT,
                # `addr_component_json` TEXT,
                # `addr_pois_json` TEXT,
                # `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

                location_id, wxopenid, addr_str, latitude, longitude, addr_info_json, addr_component_json, addr_pois_json, create_time_array = record
                create_time = create_time_array.strftime("%Y-%m-%d %H:%M:%S")
                # now_time = now_time.strftime('%Y-%m-%d %H:%M:%S')
                # d1 = datetime.datetime.strptime('2017-10-16 19:21:22', '%Y-%m-%d %H:%M:%S')
                # d2 = datetime.datetime.strptime(now_time, '%Y-%m-%d %H:%M:%S')
                # create_time = create_time.strftime('%Y-%m-%d %H:%M:%S')
                item = {}
                item['location_id']=location_id,
                item['wxopenid']=wxopenid,
                item['addr_str']=addr_str,
                item['addr_info_json']=addr_info_json,
                item['addr_component_json']=addr_component_json,
                item['addr_pois_json']=addr_pois_json,
                item['create_time']=create_time
                record_list.append(item)
                # record_list.insert(0,item)
            res = dict(
                data=record_list,
                errMsg='request:ok',
                code=0,
            )
            return jsonify(res)
            # else:
            #     return jsonify('wild wxopenid')
        except Exception as e:
            print('995-995 function:::::list_location_by_wxopenid::::type(e):::e:: {}:{}'.format(type(e), e))
            res = dict(
                data=None,
                errMsg='974-974 异常',
                code=1,
            )
            return jsonify(res)
    else:
        res = dict(
            data=None,
            errMsg='981-981 需要用POST方法',
            code=1,
        )
        return jsonify(res)


if __name__ == '__main__':
    # db = None
    db.create_all()
    app.run(host='0.0.0.0', port=56789, debug=True)
